# coding: utf-8
from sqlalchemy import Column, DateTime, Integer, MetaData, String, Table, func, INTEGER, DATETIME, select, or_, JSON
from sqlalchemy.dialects.mysql import TINYINT, VARCHAR

from config import server_config
from database.db_manager import db_session

base_metadata = MetaData()

roles = Table(
    'roles', base_metadata,
    Column('id', Integer, primary_key=True),
    Column('role', String(50), nullable=False),
    Column('create_time', DateTime, nullable=False)
)

users = Table(
    'users', base_metadata,
    Column('id', Integer, primary_key=True),
    Column('username', VARCHAR(20), nullable=False),
    Column('roles', VARCHAR(100)),
    Column('mobile', VARCHAR(20), nullable=False),
    Column('created_at', DateTime, nullable=False, default=func.now()),
    Column('deleted_at', DateTime),
    Column('password', VARCHAR(500), nullable=False, comment='$划分'),
    Column('updated_at', DateTime, default=func.now(), onupdate=func.now()),
    Column('gender', Integer, nullable=False, comment='0是女,1是男'),
    Column('birthday', DateTime)
)


goods = Table(
    'goods', base_metadata,
    Column('id', Integer, primary_key=True),
    Column('goods_number', VARCHAR(20), nullable=False),
    Column('goods_cn_name', VARCHAR(200)),
    Column('goods_en_name', VARCHAR(200)),
    Column('created_at', DateTime, nullable=False, default=func.now()),
    Column('deleted_at', DateTime),
    Column('updated_at', DateTime, default=func.now(), onupdate=func.now()),
    Column('goods_image', VARCHAR(500), nullable=False),
    Column('goods_price', Integer, nullable=False, comment="所有的数都乘1000"),
    Column('factory_name', VARCHAR(200)),
    Column('factory_link', VARCHAR(500), nullable=False),
    Column('goods_carton_size', VARCHAR(50)),
    Column('goods_carton_number', Integer),
    Column('goods_carton_weight', Integer, comment="所有的数都乘1000"),
    Column('goods_size', VARCHAR(50)),
    Column('goods_weight', Integer, comment="所有的数都乘1000"),
)

base_metadata.create_all(server_config.db_engine)

class DBCustomQuery:
    def __init__(self, table, exclude_columns=[]):
        self.table = table
        self.exclude_columns = exclude_columns
        self._query = select(model_columns(self.table, self.exclude_columns))
        self.db_session = db_session()

    def where(self, scope):
        self._query = self._query.where(scope)
        return self

    def or_(self, scope):
        self._query = self._query.where(or_(scope))
        return self

    def filter_deleted(self):
        for column in self.table.columns:
            if str(column.name) == "deleted_at":
                self._query = self._query.where(self.table.c.deleted_at.is_(None))
        return self

    def paginate(self, page=1, per_page=10):
        if page >= 999:
            page = 999
        if per_page >= 50:
            per_page = 50
        offset = (page - 1) * per_page
        self._query = self._query.offset(offset).limit(per_page)
        return self

    def fetchone(self):
        return self.db_session.execute(self._query).fetchone()

    def fetchall(self):
        return self.db_session.execute(self._query).fetchall()


    def scalar(self):
        return self.db_session.execute(self._query).scalar()

    def count(self):
        self._query = self._query.with_only_columns([func.count()])
        return self




def model_columns(model, exclude_column: list):
    model_col = filter(
        lambda column: column.name not in exclude_column,
        list(model.columns)
    )
    return model_col


def split_roles(user_info):
    if isinstance(user_info, dict) and user_info.get("roles"):
        user_info["roles"] = user_info["roles"].split(",")
    if isinstance(user_info, list):
        for user in user_info:
            if user.get("roles"):
                user["roles"] = user["roles"].split(",")
